package com.map.recreation.co.provider;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Matt on 8/21/2014.
 */
public class RecreationDB extends SQLiteOpenHelper {
    private static String TAG = "RecreationDB";

    private static String DATABASENAME = "recreationdb";
//    private static String SQL_CREATE_SCRIPT = "create_recreation_db.sql";
    private static String SQL_CREATE_SCRIPT = "convertcsv.sql";
    private static int VERSION = 1;

    public static final String FIELD_ID = "_id";
    public static final String FIELD_NAME = "name";
    public static final String FIELD_LAT = "lat";
    public static final String FIELD_LON = "lon";
    public static final String FIELD_WEB_ADDRESS = "web_addr";
    public static final String FIELD_STATE = "state";
    public static final String FIELD_CITY = "city";
    public static final String FIELD_COUNTY = "county";
    public static final String FIELD_ZIP = "zip";
    public static final String FIELD_PHONE = "phone";

    private static final String DATABASE_TABLE = "camp_sites";
    private SQLiteDatabase recreationDB;
    private Context context;

    public RecreationDB(Context context) {
        super(context, DATABASENAME, null, VERSION);
        this.context = context;
        recreationDB = getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        executeSqlScript(db, SQL_CREATE_SCRIPT);
    }

    private void executeSqlScript(SQLiteDatabase db, String sqlScript) {
        InputStream is = null;
        BufferedReader br = null;

        db.beginTransaction();

        try {
            is = context.getAssets().open(sqlScript);

            br = new BufferedReader(new InputStreamReader(is, "UTF-8"));

            String line;
            while((line = br.readLine()) != null) {
                if((line.length() > 0) && !line.startsWith("--")) {
                    db.execSQL(line);
                }
            }

            db.setTransactionSuccessful();
        }
        catch (IOException e) {
            Log.e(TAG, "Unable to execute script " + sqlScript, e);
        }
        finally {
            if(br != null) {
                try {
                    br.close();
                } catch (IOException e) {
                    Log.e(TAG, "Error closing buffered reader", e);
                }

                if(is != null) {
                    try {
                        is.close();
                    } catch (IOException e) {
                        Log.e(TAG, "Error closing input stream.", e);
                    }
                }
            }

            db.endTransaction();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO
    }

    /**
     * Method to return all the camp sites from database.
     * @return List
     */
    public List<CampSite> getAllCampSites() {
        List<CampSite> sites = new ArrayList<CampSite>();
        String selectQuery = "SELECT  * FROM " + DATABASE_TABLE;

        Cursor cursor = recreationDB.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                double lat = cursor.getDouble(cursor.getColumnIndex(FIELD_LAT));
                double lon = cursor.getDouble(cursor.getColumnIndex(FIELD_LON));
                int id = cursor.getInt(cursor.getColumnIndex(FIELD_ID));
                CampSite campSite = new CampSite(id, lat, lon);

                campSite.setName(cursor.getString((cursor.getColumnIndex(FIELD_NAME))));
                campSite.setPhone(cursor.getInt((cursor.getColumnIndex(FIELD_PHONE))));
                campSite.setState(cursor.getString((cursor.getColumnIndex(FIELD_STATE))));
//                campSite.setWeb_address(cursor.getString((cursor.getColumnIndex(FIELD_WEB_ADDRESS))));
//                campSite.setCity(cursor.getString((cursor.getColumnIndex(FIELD_CITY))));
//                campSite.setCounty(cursor.getString((cursor.getColumnIndex(FIELD_COUNTY))));
//                campSite.setZip(cursor.getInt((cursor.getColumnIndex(FIELD_ZIP))));


                sites.add(campSite);
            } while (cursor.moveToNext());
        }

        return sites;
    }
}
